from typing import *
from dataclasses import *
import pymysql
import json

db = pymysql.connect(host='localhost', user='commission', password='ULHVg7xdhHQmSXkbt', database='commission')


def query(sql):
  print(sql)
  cursor = db.cursor()
  cursor.execute(sql)
  result = cursor.fetchall()
  cursor.close()
  return result


data = []
for r1 in query('select * from t_city_code where level=1 order by code'):
  d1 = {'value': r1[2], 'label': r1[3], 'children': []}
  for r2 in query('select * from t_city_code where level=2 and p_id=%d order by code' % r1[0]):
    d2 = {'value': r2[2], 'label': r2[3], 'children': []}
    for r3 in query('select * from t_city_code where level=3 and p_id=%d order by code' % r2[0]):
      d3 = {'value': r3[2], 'label': r3[3]}
      d2['children'].append(d3)
    if len(d2['children']) == 0:
      d2['children'].append({'value': r2[2], 'label': r2[3]})
    d1['children'].append(d2)
  if len(d1['children']) == 0:
    continue
  data.append(d1)

with open('htCityCode.json', 'wt', encoding='UTF-8') as fp:
  content = json.dumps(data, ensure_ascii=False)
  fp.write(content)
